using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace SpringWaterDataAccess

{

/// <summary>

/// Summary description for bookshelf.

/// </summary>

	public class SQLAccess

	{
		private const string DBConenctionKey = "DBConnStr";

		public SQLAccess()
		{
		}
		#region BookShelf Done
		/* input: 
		 * email: email address
		 * wordID: the word ID to add
		 * return: true -- success
		 * false-- failed. 
		 * Note: when failed, check msg for detail error message, 
		 * and you can display them differently
		 * */
		public void AddOneWord(string email, int wordID)
		{
			string storeProcedureName = "sp_addbook";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.VarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;

			SqlParameter wordPara = cmd.CreateParameter();
			wordPara.ParameterName = "@wordID";
			wordPara.SqlDbType = SqlDbType.Int;
			wordPara.Value = wordID;
			cmd.Parameters.Add(emailPara);
			cmd.Parameters.Add(wordPara);

			ExecuteNonReturnCmd(cmd);
		}

		public void IncreaseNumberClicks(int wordID) 
		{
			string storeProcedureName = "sp_IncNbrofClick";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter wordPara = cmd.CreateParameter();
			wordPara.ParameterName = "@wordID";
			wordPara.SqlDbType = SqlDbType.Int;
			wordPara.Value = wordID;
			 
			cmd.Parameters.Add(wordPara);

			ExecuteNonReturnCmd(cmd);
		}

	
		/* input: user_email: email address
		* wordID: the word ID to delete
		* return: true -- success
		* false-- failed. 
		* Note: when failed, check msg for detail error message, 
		* and you can display them differently
		* */
		public void DeleteWord(string email, int wordID)
		{
			this.DeleteWord(email, wordID.ToString());
		}

		public void DeleteWord(string email, string words)
		{
			string storeProcedureName = "sp_deletebook";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.VarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;

			SqlParameter wordsPara = cmd.CreateParameter();
			wordsPara.ParameterName = "@wordID";
			wordsPara.SqlDbType = SqlDbType.NVarChar;
			wordsPara.Size = 255;
			wordsPara.Value = words;
			cmd.Parameters.Add(emailPara);
			cmd.Parameters.Add(wordsPara);

			ExecuteNonReturnCmd(cmd );
		}

		/* input: user_email: email address
		* return: object -- success
		* null -- failed. 
		* Note: when failed, check msg for detail error message, 
		* and you can display them differently
		* */
		public DataSet RetrieveAllWords(string email)
		{
			string storeProcedureName = "sp_retrievebooks";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.VarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;

			cmd.Parameters.Add(emailPara);

			return ExecuteReturnCmd(cmd );
		}
		/* input: user_email: email address
		* return: object -- success
		* null -- failed. 
		* Note: when failed, check msg for detail error message, 
		* and you can display them differently
		* */
		public DataSet getWordsByStatus(string status)
		{
			string storeProcedureName = "sp_GetArticle";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@status";
			statusPara.Size = 100;
			statusPara.Value = status;

			cmd.Parameters.Add(statusPara);

			return ExecuteReturnCmd(cmd );
		}


		/* input: user_email: email address
		* return: object -- success
		* null -- failed. 
		* Note: when failed, check msg for detail error message, 
		* and you can display them differently
		* */
		public DataSet getWordsByStatusAndCategory(string status, string category)
		{
			string storeProcedureName = "sp_GetArtByCatSta";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@status";
			statusPara.Size = 10;
			statusPara.Value = status;

			SqlParameter catPara = cmd.CreateParameter();
			catPara.SqlDbType = SqlDbType.VarChar;
			catPara.ParameterName = "@Category";
			catPara.Size = 50;
			catPara.Value = category;

			cmd.Parameters.Add(statusPara);
			cmd.Parameters.Add(catPara);

			return ExecuteReturnCmd(cmd );
		}

		public DataSet getForumPostsByStatus(string status)
		{
			string storeProcedureName = "sp_GetForumPostsByStatus";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@status";
			statusPara.Size = 10;
			statusPara.Value = status;

			cmd.Parameters.Add(statusPara);

			return ExecuteReturnCmd(cmd);
		}

		public DataSet getTopWords(int howmany) {
		 	string storeProcedureName = "sp_GetTopArticles";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter topPara = cmd.CreateParameter();
			topPara.SqlDbType = SqlDbType.Int;
			topPara.ParameterName = "@GetTopArticle";
			topPara.Value = howmany;

			cmd.Parameters.Add(topPara);

			return ExecuteReturnCmd(cmd );
		}




		public DataSet getWordByID(int wordID)
		{
			string storeProcedureName = "sp_GetArticleByWordID";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter idPara = cmd.CreateParameter();
			idPara.SqlDbType = SqlDbType.Int;
			idPara.ParameterName = "@WordID";
			idPara.Value = wordID;

			cmd.Parameters.Add(idPara);

			return ExecuteReturnCmd(cmd );
		}

		//Function is get Articles by the CategoryID and the status = approved
		public DataSet getWordsByCategoryID(int CatID)
		{
			//Hack!!! Need to change it to SP
			string strSql = 
"SELECT wordid, title, originauthor,convert(char(12),w.LastModifiedDateTime,101) ld, partnerCode FROM word w, partner p WHERE CategoryID = "+CatID.ToString()+" and (status = 'archived' or status = 'approved') and w.partnerid = p.partnerid";

			SqlCommand cmd = new SqlCommand(strSql);

			return ExecuteReturnCmd(cmd );

	    }
		#endregion

		#region Words
	
		public void ChangeWordStatus(int wordID, int userID, string newStatus)
		{
			string storeProcedureName = "sp_ChangeWordStatus";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter wordIDPara = cmd.CreateParameter();
			wordIDPara.SqlDbType = SqlDbType.Int;
			wordIDPara.ParameterName = "@WordId";
			wordIDPara.Value = wordID;

			SqlParameter userIDPara = cmd.CreateParameter();
			userIDPara.SqlDbType = SqlDbType.Int;
			userIDPara.ParameterName = "@UserID";
			userIDPara.Value = userID;

			SqlParameter newStatusPara = cmd.CreateParameter();
			newStatusPara.SqlDbType = SqlDbType.VarChar;
			newStatusPara.ParameterName = "@Status";
			newStatusPara.Size = 10;
			newStatusPara.Value = newStatus;

			cmd.Parameters.Add(wordIDPara);
			cmd.Parameters.Add(userIDPara);
			cmd.Parameters.Add(newStatusPara);
			
			ExecuteNonReturnCmd(cmd);
		}

		/* input: 
		 * userID who is adding
		 * return: true  -- success
		 *         false -- failed 
		 * Note: when failed, check msg for detail error message, 
		 * and you can display them differently
		 * */
		public void AddWord(string title, int categoryID,
			int partnerID, string originAuthor, string description,
			string url, int userID,string recommendBy,DateTime pubDate)
		{
			string storeProcedureName = "sp_AddWord";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter titlePara = cmd.CreateParameter();
			titlePara.SqlDbType = SqlDbType.NVarChar;
			titlePara.ParameterName = "@title";
			titlePara.Size = 255;
			titlePara.Value = title;

			SqlParameter categoryPara = cmd.CreateParameter();
			categoryPara.ParameterName = "@categoryID";
			categoryPara.SqlDbType = SqlDbType.Int;
			categoryPara.Value = categoryID;
			
			SqlParameter partnerPara = cmd.CreateParameter();
			partnerPara.SqlDbType = SqlDbType.Int;
			partnerPara.ParameterName = "@partnerID";
			partnerPara.Value = partnerID;

			SqlParameter originAuthorPara = cmd.CreateParameter();
			originAuthorPara.SqlDbType = SqlDbType.NVarChar;
			originAuthorPara.ParameterName = "@originAuthor";
			originAuthorPara.Size = 100;
			originAuthorPara.Value = originAuthor;

			System.Console.WriteLine(originAuthor);

	        SqlParameter descriptionPara = cmd.CreateParameter();
			descriptionPara.SqlDbType = SqlDbType.NVarChar;
			descriptionPara.ParameterName = "@description";
			descriptionPara.Size = 255;
			descriptionPara.Value = description;

			SqlParameter urlPara = cmd.CreateParameter();
			urlPara.SqlDbType = SqlDbType.VarChar;
			urlPara.ParameterName = "@url";
			urlPara.Size = 255;
			urlPara.Value = url;

			SqlParameter userIDPara = cmd.CreateParameter();
			userIDPara.ParameterName = "@userID";
			userIDPara.SqlDbType = SqlDbType.Int;
			userIDPara.Value = userID;

			SqlParameter recommendByPara = cmd.CreateParameter();
			recommendByPara.SqlDbType = SqlDbType.NVarChar;
			recommendByPara.ParameterName = "@RecommedBy";
			recommendByPara.Size = 100;
			recommendByPara.Value = recommendBy;

			SqlParameter pubDatePara = cmd.CreateParameter();
			pubDatePara.SqlDbType = SqlDbType.DateTime;
			pubDatePara.ParameterName = "@PublishDate";
			pubDatePara.Value = pubDate;

			cmd.Parameters.Add(titlePara);
			cmd.Parameters.Add(categoryPara);
			cmd.Parameters.Add(partnerPara);
			cmd.Parameters.Add(originAuthorPara);
			cmd.Parameters.Add(descriptionPara);
			cmd.Parameters.Add(urlPara);
			cmd.Parameters.Add(userIDPara);
			cmd.Parameters.Add(recommendByPara);
			cmd.Parameters.Add(pubDatePara);
			
			ExecuteNonReturnCmd(cmd);
		}
		#endregion
		
		#region Partners

		public DataSet getPartner(int partnerID)
		{
			string storeProcedureName = "sp_GetPartner";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter idPara = cmd.CreateParameter();
			idPara.SqlDbType = SqlDbType.Int;
			idPara.ParameterName = "@PartnerID";
			idPara.Value = partnerID;

			cmd.Parameters.Add(idPara);

			return ExecuteReturnCmd(cmd );
		}

		public DataSet getAllPartners()
		{
			string sSQL = "SELECT PartnerID, PartnerCode, Description, " + 
				"CreatedBy FROM  Partner";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = sSQL;

			return ExecuteReturnCmd(cmd );
		}	

		public void DeletePartner(int PartnerID)
		{
			string sSQL = "Delete From Partner Where PartnerID = " + PartnerID.ToString();
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;
			ExecuteNonReturnCmd(cmd );
		}

		public void UpdatePartner(int PartnerID, string PartnerCode, string Description, string CreatedBy)
		{
			string sSQL = "Update Partner Set PartnerCode = @PartnerCode, " +
				"Description = @Description, CreatedBy = @CreatedBy " + "Where PartnerID = @PartnerID";
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			SqlParameter paraPartCode = cmd.CreateParameter();
			paraPartCode.SqlDbType = SqlDbType.NVarChar;
			paraPartCode.ParameterName = "@PartnerCode";
			paraPartCode.Value = PartnerCode;
			cmd.Parameters.Add(paraPartCode);

			SqlParameter paraDesc = cmd.CreateParameter();
			paraDesc.SqlDbType = SqlDbType.NVarChar;
			paraDesc.ParameterName = "@Description";
			paraDesc.Value = Description;
			cmd.Parameters.Add(paraDesc);

			SqlParameter paraCreate = cmd.CreateParameter();
			paraCreate.SqlDbType = SqlDbType.NVarChar;
			paraCreate.ParameterName = "@CreatedBy";
			paraCreate.Value = CreatedBy;
			cmd.Parameters.Add(paraCreate);

			SqlParameter idPara = cmd.CreateParameter();
			idPara.SqlDbType = SqlDbType.Int;
			idPara.ParameterName = "@PartnerID";
			idPara.Value = PartnerID;

			cmd.Parameters.Add(idPara);

			ExecuteNonReturnCmd(cmd );
		}
		
		//Created for inserting partner data in admin tool.	JerryC
		public void InsertPartner(string PartnerCode, string Description, string User, string URL)
		{
			string sSQL = "Insert Into Partner (PartnerID, PartnerCode, Description, " + 
				"URL, CreatedBy, CreatedDateTime,LastModifiedBy, LastModifiedDateTime) " + 
				"VALUES (@pID, @pCode, @desc, @URL, @uID, @date, @uID, @date)";
			
			SqlCommand cmd = new SqlCommand("Select MAX(PartnerID) As maxID From Partner");
			cmd.CommandType = CommandType.Text;

			DataSet ds = new DataSet();
			ds = ExecuteReturnCmd(cmd );
			int PartnerID = (int) ds.Tables[0].Rows[0][0] + 1;

			cmd.CommandText = sSQL;

			SqlParameter paraPartCode = cmd.CreateParameter();
			paraPartCode.SqlDbType = SqlDbType.NVarChar;
			paraPartCode.ParameterName = "@pCode";
			paraPartCode.Value = PartnerCode;
			cmd.Parameters.Add(paraPartCode);

			SqlParameter paraDesc = cmd.CreateParameter();
			paraDesc.SqlDbType = SqlDbType.NVarChar;
			paraDesc.ParameterName = "@desc";
			paraDesc.Value = Description;
			cmd.Parameters.Add(paraDesc);

			SqlParameter paraURL = cmd.CreateParameter();
			paraURL.SqlDbType = SqlDbType.NVarChar;
			paraURL.ParameterName = "@URL";
			paraURL.Value = URL;
			cmd.Parameters.Add(paraURL);

			SqlParameter paraCreate = cmd.CreateParameter();
			paraCreate.SqlDbType = SqlDbType.NVarChar;
			paraCreate.ParameterName = "@uID";
			paraCreate.Value = User;
			cmd.Parameters.Add(paraCreate);

			SqlParameter idPara = cmd.CreateParameter();
			idPara.SqlDbType = SqlDbType.Int;
			idPara.ParameterName = "@pID";
			idPara.Value = PartnerID;
			cmd.Parameters.Add(idPara);

			SqlParameter datePara = cmd.CreateParameter();
			datePara.SqlDbType = SqlDbType.DateTime;
			datePara.ParameterName = "@date";
			datePara.Value = DateTime.Now;

			cmd.Parameters.Add(datePara);

			ExecuteNonReturnCmd(cmd );
		}
		#endregion

		#region Forums

		
		public DataSet getForumData()
		{
			string sSQL = "SELECT ForumID, ForumName, RoleID " + 
				"FROM  Forums";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = sSQL;

			return ExecuteReturnCmd(cmd );
		}	

	
		public void UpdateForumData(int ForumID, string ForumName, int RoleID)
		{
			
			string sSQL = "Update Forums Set ForumName = @ForumName, " +
				"RoleID = @RoleID Where ForumID = @ForumID";
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			SqlParameter paraName = cmd.CreateParameter();
			paraName.SqlDbType = SqlDbType.NVarChar;
			paraName.ParameterName = "@ForumName";
			paraName.Value = ForumName;
			cmd.Parameters.Add(paraName);

			
			SqlParameter idPara = cmd.CreateParameter();
			idPara.SqlDbType = SqlDbType.Int;
			idPara.ParameterName = "@ForumID";
			idPara.Value = ForumID;

			cmd.Parameters.Add(idPara);

			SqlParameter idPara1 = cmd.CreateParameter();
			idPara1.SqlDbType = SqlDbType.Int;
			idPara1.ParameterName = "@RoleID";
			idPara1.Value = RoleID;

			cmd.Parameters.Add(idPara1);

			ExecuteNonReturnCmd(cmd );
		}

		//Created for inserting Forum data in admin tool.	JerryC
		public void InsertForum(int ForumID, string ForumName, int RoleID)
		{
			string sSQL = "Insert Into Forums (ForumID, ForumName, RoleID) " + 
				"VALUES (@ForumID, @ForumName, @RoleID)";
			
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			SqlParameter paraName = cmd.CreateParameter();
			paraName.SqlDbType = SqlDbType.NVarChar;
			paraName.ParameterName = "@ForumName";
			paraName.Value = ForumName;
			cmd.Parameters.Add(paraName);

			
			SqlParameter idPara = cmd.CreateParameter();
			idPara.SqlDbType = SqlDbType.Int;
			idPara.ParameterName = "@ForumID";
			idPara.Value = ForumID;

			cmd.Parameters.Add(idPara);

			SqlParameter idPara1 = cmd.CreateParameter();
			idPara1.SqlDbType = SqlDbType.Int;
			idPara1.ParameterName = "@RoleID";
			idPara1.Value = RoleID;

			cmd.Parameters.Add(idPara1);

			ExecuteNonReturnCmd(cmd );
		}
		#endregion

		#region ForumCategories

		
		public DataSet getForumCategoriesData()
		{
			string sSQL = "SELECT * " + //CategoryID, CategoryName, ForumID" + 
				"FROM  ForumCategories";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = sSQL;

			return ExecuteReturnCmd(cmd );
		}	

	
		public void UpdateForumCategoryData(int CategoryID, int ForumID)
		{
			
			string sSQL = "Update ForumCategories Set ForumID = " + 
				ForumID.ToString() + " Where CategoryID = " + CategoryID.ToString();
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;
			ExecuteNonReturnCmd(cmd );
		}
		#endregion

		#region Categorys

		public DataSet getCategory(int CategoryID)
		{
			string storeProcedureName = "sp_GetCategory";
			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = storeProcedureName;

			SqlParameter idPara = cmd.CreateParameter();
			idPara.SqlDbType = SqlDbType.Int;
			idPara.ParameterName = "@CategoryID";
			idPara.Value = CategoryID;

			cmd.Parameters.Add(idPara);

			return ExecuteReturnCmd(cmd );
		}

		public DataSet getAllCategories()
		{
			//string storeProcedureName = "sp_GetCategories";
			string sSelect = "Select CategoryID, CategoryName From ForumCategories";
			SqlCommand cmd = new SqlCommand(sSelect);
			cmd.CommandType = CommandType.Text;
			//cmd.CommandText = storeProcedureName;

			return ExecuteReturnCmd(cmd );
		}	
		#endregion


		#region Users
		public DataSet LoginUser(string email, string password)
		{
			string storeProcedureName = "sp_login";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;
			cmd.Parameters.Add(emailPara);

			SqlParameter passwordPara = cmd.CreateParameter();
			passwordPara.SqlDbType = SqlDbType.NVarChar;
			passwordPara.ParameterName = "@password";
			passwordPara.Size = 50;
			passwordPara.Value = password;
			cmd.Parameters.Add(passwordPara);

			return this.ExecuteReturnCmd(cmd);

		}

		public void DeleteUser(string email)
		{
			/*
			string storeProcedureName = "sp_deleteuser";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;
			cmd.Parameters.Add(emailPara);
			*/
			string sSQL = "Delete From Users Where Email = '" + email + "'";
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			this.ExecuteNonReturnCmd(cmd);
		}

		
		public DataSet GetUser(string email)
		{
			string storeProcedureName = "sp_getuser";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;
			cmd.Parameters.Add(emailPara);

			return this.ExecuteReturnCmd(cmd);
		}
		
		//Created for edting the Users table in Admin tool -- JerryC
		public DataSet GetUsers()
		{
			string sSQL = "Select UserID, RoleID, Email, Password, NickName, Type, Status, Description From Users";

			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			return this.ExecuteReturnCmd(cmd);
		}
		//Created for edting the Users table in Admin tool -- JerryC
		public DataSet GetUserPasswordByID(int UserID)
		{
			string sSQL = "Select Password From Users Where UserID = " + UserID.ToString();

			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			return this.ExecuteReturnCmd(cmd);
		}

		//Created for edting the Users table in Admin tool -- JerryC
		public string GetUserPasswordByNickName(string NickName)
		{
			string sSQL = "Select Password From Users Where NickName = @NickName";

			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			SqlParameter nickNamePara = cmd.CreateParameter();
			nickNamePara.SqlDbType = SqlDbType.NVarChar;
			nickNamePara.ParameterName = "@NickName";
			nickNamePara.Size = 50;
			nickNamePara.Value = NickName;
			cmd.Parameters.Add(nickNamePara);

			DataSet dsUser = new DataSet();
			dsUser = this.ExecuteReturnCmd(cmd);
			return (dsUser.Tables[0].Rows[0]["Password"].ToString());
		}

			public DataSet GetUserByID(int userID)
			{
				string storeProcedureName = "sp_GetUserByID";
				SqlCommand cmd = new SqlCommand(storeProcedureName);
				cmd.CommandType = CommandType.StoredProcedure;

				SqlParameter userIDPara = cmd.CreateParameter();
				userIDPara.SqlDbType = SqlDbType.Int;
				userIDPara.ParameterName = "@UserID";
				userIDPara.Value = userID;
				cmd.Parameters.Add(userIDPara);

				return this.ExecuteReturnCmd(cmd);
			}

		public void AddUser(string email, 
			  string password, string nickName, string recommendBy, string faith,string ipAddress)
		{
			string storeProcedureName = "sp_registeruser";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;
			cmd.Parameters.Add(emailPara);

			SqlParameter passwordPara = cmd.CreateParameter();
			passwordPara.SqlDbType = SqlDbType.NVarChar;
			passwordPara.ParameterName = "@password";
			passwordPara.Size = 50;
			passwordPara.Value = password;
			cmd.Parameters.Add(passwordPara);

			SqlParameter nickNamePara = cmd.CreateParameter();
			nickNamePara.SqlDbType = SqlDbType.NVarChar;
			nickNamePara.ParameterName = "@nickname";
			nickNamePara.Size = 50;
			nickNamePara.Value = nickName;
			cmd.Parameters.Add(nickNamePara);

			SqlParameter recommendByNamePara = cmd.CreateParameter();
			recommendByNamePara.SqlDbType = SqlDbType.NVarChar;
			recommendByNamePara.ParameterName = "@recommendBy";
			recommendByNamePara.Size = 50;
			recommendByNamePara.Value = recommendBy;
			cmd.Parameters.Add(recommendByNamePara);

			SqlParameter faithPara = cmd.CreateParameter();
			faithPara.SqlDbType = SqlDbType.VarChar;
			faithPara.ParameterName = "@faith";
			faithPara.Value = faith;
			cmd.Parameters.Add(faithPara);

            SqlParameter ipPara = cmd.CreateParameter();
            ipPara.SqlDbType = SqlDbType.VarChar;
            ipPara.ParameterName = "@ipAddress";
            ipPara.Value = ipAddress;
            cmd.Parameters.Add(ipPara);


			this.ExecuteNonReturnCmd(cmd);

		}

		public void ChangeUserInfo(string email, string oldpassword, 
			  string newpassword, string nickName, string status, 
			  string type,string description)
		{
			string storeProcedureName = "sp_ChangeUserInfo";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;
			cmd.Parameters.Add(emailPara);

			SqlParameter passwordPara = cmd.CreateParameter();
			passwordPara.SqlDbType = SqlDbType.NVarChar;
			passwordPara.ParameterName = "@oldpassword";
			passwordPara.Size = 50;
			passwordPara.Value = oldpassword;
			cmd.Parameters.Add(passwordPara);

			SqlParameter newpasswordPara = cmd.CreateParameter();
			newpasswordPara.SqlDbType = SqlDbType.NVarChar;
			newpasswordPara.ParameterName = "@newpassword";
			newpasswordPara.Size = 50;
			newpasswordPara.Value = newpassword;
			cmd.Parameters.Add(newpasswordPara);

			SqlParameter nickNamePara = cmd.CreateParameter();
			nickNamePara.SqlDbType = SqlDbType.NVarChar;
			nickNamePara.ParameterName = "@nickname";
			nickNamePara.Size = 50;
			nickNamePara.Value = nickName;
			cmd.Parameters.Add(nickNamePara);

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@status";
			statusPara.Value = status;
			cmd.Parameters.Add(statusPara);

			SqlParameter typePara = cmd.CreateParameter();
			typePara.SqlDbType = SqlDbType.VarChar;
			typePara.ParameterName = "@type";
			typePara.Value = type;
			cmd.Parameters.Add(typePara);

			SqlParameter descriptionPara = cmd.CreateParameter();
			descriptionPara.SqlDbType = SqlDbType.NVarChar;
			descriptionPara.ParameterName = "@description";
			descriptionPara.Value = description;
			cmd.Parameters.Add(descriptionPara);

			this.ExecuteNonReturnCmd(cmd);
		}

		//Created for supporting Admin.EditUserRole web form	--JerryC
		public void UpdateUserInfo(int UserID, int RoleID, string Email,  
			string Password, string NickName, string Status, 
			string Type,string Description)
		{
			string sSQL = "Update Users Set RoleID = @RoleID, Email = @Email, Password = @Password," +
							"NickName = @NickName, Status = @Status, Type = @Type, Description = @Description WHERE " + 
								"UserID = @UserID";
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			SqlParameter userIDPara = cmd.CreateParameter();
			userIDPara.SqlDbType = SqlDbType.Int;
			userIDPara.ParameterName = "@UserID";
			userIDPara.Value = UserID;
			cmd.Parameters.Add(userIDPara);

			SqlParameter roleIDPara = cmd.CreateParameter();
			roleIDPara.SqlDbType = SqlDbType.Int;
			roleIDPara.ParameterName = "@RoleID";
			roleIDPara.Value = RoleID;
			cmd.Parameters.Add(roleIDPara);

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@Email";
			emailPara.Size = 100;
			emailPara.Value = Email;
			cmd.Parameters.Add(emailPara);

			SqlParameter passwordPara = cmd.CreateParameter();
			passwordPara.SqlDbType = SqlDbType.NVarChar;
			passwordPara.ParameterName = "@Password";
			passwordPara.Size = 50;
			passwordPara.Value = Password;
			cmd.Parameters.Add(passwordPara);

			

			SqlParameter nickNamePara = cmd.CreateParameter();
			nickNamePara.SqlDbType = SqlDbType.NVarChar;
			nickNamePara.ParameterName = "@Nickname";
			nickNamePara.Size = 50;
			nickNamePara.Value = NickName;
			cmd.Parameters.Add(nickNamePara);

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@Status";
			statusPara.Value = Status;
			cmd.Parameters.Add(statusPara);

			SqlParameter typePara = cmd.CreateParameter();
			typePara.SqlDbType = SqlDbType.VarChar;
			typePara.ParameterName = "@Type";
			typePara.Value = Type;
			cmd.Parameters.Add(typePara);

			SqlParameter descriptionPara = cmd.CreateParameter();
			descriptionPara.SqlDbType = SqlDbType.NVarChar;
			descriptionPara.ParameterName = "@Description";
			descriptionPara.Value = Description;
			cmd.Parameters.Add(descriptionPara);

			this.ExecuteNonReturnCmd(cmd);
		}

		//Created for supporting Admin.EditUserRole web form	--JerryC
		public void InsertUserInfo(string CreatedBy, int RoleID, string Email,  
			string Password, string NickName, string Status, 
			string Type, string Description)
		{
			string sSQL = "Insert Into Users (RoleID, Email, Password," +
				"NickName, Status, Type, RegisterDate, LastLoginDate, Description, CreatedBy," + 
				"CreatedDateTime, ModifiedBy, ModifiedDateTime) VALUES " + 
				"(@RoleID, @Email, @Password,@NickName, @Status, @Type, @date, @date, @Description, " +
				"@user, @date, @user, @date)"; 
				
			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			SqlParameter userPara = cmd.CreateParameter();
			userPara.SqlDbType = SqlDbType.VarChar;
			userPara.ParameterName = "@user";
			userPara.Size = 50;
			userPara.Value = CreatedBy;
			cmd.Parameters.Add(userPara);

			SqlParameter datePara = cmd.CreateParameter();
			datePara.SqlDbType = SqlDbType.DateTime;
			datePara.ParameterName = "@date";
			datePara.Value = DateTime.Now;
			cmd.Parameters.Add(datePara);

			SqlParameter roleIDPara = cmd.CreateParameter();
			roleIDPara.SqlDbType = SqlDbType.Int;
			roleIDPara.ParameterName = "@RoleID";
			roleIDPara.Value = RoleID;
			cmd.Parameters.Add(roleIDPara);

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@Email";
			emailPara.Size = 100;
			emailPara.Value = Email;
			cmd.Parameters.Add(emailPara);

			SqlParameter passwordPara = cmd.CreateParameter();
			passwordPara.SqlDbType = SqlDbType.NVarChar;
			passwordPara.ParameterName = "@Password";
			passwordPara.Size = 50;
			passwordPara.Value = Password;
			cmd.Parameters.Add(passwordPara);

			

			SqlParameter nickNamePara = cmd.CreateParameter();
			nickNamePara.SqlDbType = SqlDbType.NVarChar;
			nickNamePara.ParameterName = "@Nickname";
			nickNamePara.Size = 50;
			nickNamePara.Value = NickName;
			cmd.Parameters.Add(nickNamePara);

			SqlParameter statusPara = cmd.CreateParameter();
			statusPara.SqlDbType = SqlDbType.VarChar;
			statusPara.ParameterName = "@Status";
			statusPara.Value = Status;
			cmd.Parameters.Add(statusPara);

			SqlParameter typePara = cmd.CreateParameter();
			typePara.SqlDbType = SqlDbType.VarChar;
			typePara.ParameterName = "@Type";
			typePara.Value = Type;
			cmd.Parameters.Add(typePara);

			SqlParameter descriptionPara = cmd.CreateParameter();
			descriptionPara.SqlDbType = SqlDbType.NVarChar;
			descriptionPara.ParameterName = "@Description";
			descriptionPara.Value = Description;
			cmd.Parameters.Add(descriptionPara);

			this.ExecuteNonReturnCmd(cmd);
		}


		public DataSet GetUserPassword(string email)
		{
			
			string storeProcedureName = "sp_getuserpassword";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@email";
			emailPara.Size = 100;
			emailPara.Value = email;
			cmd.Parameters.Add(emailPara);
			
			return this.ExecuteReturnCmd(cmd);
		}

		#endregion

		#region Role
		//Created for edting the Users table in Admin tool -- JerryC
		public DataSet GetRoles()
		{
			string sSQL = "Select RoleID, RoleName From Role";

			SqlCommand cmd = new SqlCommand(sSQL);
			cmd.CommandType = CommandType.Text;

			return this.ExecuteReturnCmd(cmd);
		}
		#endregion


		#region Comments Done
		public void InsertComment(int wordID, string subject, string comment, string email, int stars)
		{
			string storeProcedureName = "sp_insertcomment";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter wordIDPara = cmd.CreateParameter();
			wordIDPara.SqlDbType = SqlDbType.Int;
			wordIDPara.ParameterName = "@wordID";
			wordIDPara.Value = wordID;
			cmd.Parameters.Add(wordIDPara);

			SqlParameter subjectPara = cmd.CreateParameter();
			subjectPara.SqlDbType = SqlDbType.NVarChar;
			subjectPara.ParameterName = "@subject";
			subjectPara.Size = 255;
			subjectPara.Value = subject;
			cmd.Parameters.Add(subjectPara);

			SqlParameter commentPara = cmd.CreateParameter();
			commentPara.SqlDbType = SqlDbType.NVarChar;
			commentPara.ParameterName = "@comment";
			commentPara.Size = 255;
			commentPara.Value = comment;
			cmd.Parameters.Add(commentPara);

			SqlParameter emailPara = cmd.CreateParameter();
			emailPara.SqlDbType = SqlDbType.NVarChar;
			emailPara.ParameterName = "@commentBy";
			emailPara.Size = 255;
			emailPara.Value = email;
			cmd.Parameters.Add(emailPara);

			SqlParameter starsPara = cmd.CreateParameter();
			starsPara.SqlDbType = SqlDbType.Int;
			starsPara.ParameterName = "@stars";
			starsPara.Value = stars;
			cmd.Parameters.Add(starsPara);

			this.ExecuteNonReturnCmd(cmd);

		}

		public void DeleteComment(int wordID, int commentID)
		{
			string storeProcedureName = "sp_deletecomment";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter wordIDPara = cmd.CreateParameter();
			wordIDPara.SqlDbType = SqlDbType.Int;
			wordIDPara.ParameterName = "@wordID";
			wordIDPara.Value = wordID;
			cmd.Parameters.Add(wordIDPara);
			
			SqlParameter commentIDPara = cmd.CreateParameter();
			commentIDPara.SqlDbType = SqlDbType.Int;
			commentIDPara.ParameterName = "@commentID";
			commentIDPara.Value = commentID;
			cmd.Parameters.Add(commentIDPara);

			this.ExecuteNonReturnCmd(cmd);
		}

		public DataSet RetrieveComments(int wordID, int startID, int nextN)
		{
			string storeProcedureName = "sp_retrievecomments";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter wordIDPara = cmd.CreateParameter();
			wordIDPara.SqlDbType = SqlDbType.Int;
			wordIDPara.ParameterName = "@wordID";
			wordIDPara.Value = wordID;
			cmd.Parameters.Add(wordIDPara);

			SqlParameter startIDPara = cmd.CreateParameter();
			startIDPara.SqlDbType = SqlDbType.Int;
			startIDPara.ParameterName = "@startID";
			startIDPara.Value = startID;
			cmd.Parameters.Add(startIDPara);

			SqlParameter nextNPara = cmd.CreateParameter();
			nextNPara.SqlDbType = SqlDbType.Int;
			nextNPara.ParameterName = "@nextN";
			nextNPara.Value = nextN;
			cmd.Parameters.Add(nextNPara);

			return this.ExecuteReturnCmd(cmd);
		}

		public void UpdateComment(int wordID, int commentID, bool agree)
		{
			string storeProcedureName = "sp_updatecomment";
			SqlCommand cmd = new SqlCommand(storeProcedureName);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter wordIDPara = cmd.CreateParameter();
			wordIDPara.SqlDbType = SqlDbType.Int;
			wordIDPara.ParameterName = "@wordID";
			wordIDPara.Value = wordID;
			cmd.Parameters.Add(wordIDPara);
			
			SqlParameter commentIDPara = cmd.CreateParameter();
			commentIDPara.SqlDbType = SqlDbType.Int;
			commentIDPara.ParameterName = "@commentID";
			commentIDPara.Value = commentID;
			cmd.Parameters.Add(commentIDPara);

			SqlParameter agreePara = cmd.CreateParameter();
			agreePara.SqlDbType = SqlDbType.Bit;
			agreePara.ParameterName = "@agreed";
			agreePara.Value = agree;
			cmd.Parameters.Add(agreePara);

			this.ExecuteNonReturnCmd(cmd);
		}

		#endregion

		#region Generic DB Access Methods
		protected  SqlConnection OpenConnection()
		{
			string connectionString = ConfigurationSettings.AppSettings[DBConenctionKey];
			SqlConnection cn = new SqlConnection();
			cn.ConnectionString = connectionString;
			cn.Open();
			return cn;
		}

		protected void ExecuteNonReturnCmd(SqlCommand cmd)
		{
			SqlConnection cn = null;
			try
			{
				cn = this.OpenConnection();
				cmd.Connection = cn;
				int rowsAffected = cmd.ExecuteNonQuery();
                //if (rowsAffected == 0 )
                //    throw new ApplicationException("No row affected, check your input");
			}
			finally
			{
				if (null!=cn) cn.Close();
			}
		}

		protected DataSet ExecuteReturnCmd(SqlCommand cmd)
		{
			SqlConnection cn = null;
			DataSet ds = null;
			try
			{
				cn = this.OpenConnection();
				cmd.Connection = cn;
				SqlDataAdapter da = new SqlDataAdapter(cmd);
				ds = new DataSet();
				da.Fill(ds);
			}
			finally
			{
				if (null!=cn) cn.Close();
			}
			return ds;
		}
		#endregion
	}

}

/*
Web.config modification:
<appSettings>

<add key="DBConnStr" value="Persist Security Info=False;User ID=sa;Password = ; Initial Catalog=dbspring;Data Source=localhost"/> 

</appSettings>

*/